package com.mapgis.service

import com.mapgis.constant.Constants

class SqlBox {


    //房屋数据同步转换过程的sql
    private val buildSql=
        s"""
          |select
          |    "ID",
          |    "BUILDINGPHOTO",
          |    "BUILDTYPE",
          |    "BUILDINGTYPE",
          |    "BUILDINGSPECIALTYPE",
          |    "BUILDINGOTHERTYPES",
          |    "CODE",
          |    "CERTIFICATIONTYPE",
          |    "CERTIFICATIONNO",
          |    "CERTIFICATIONADDRESS",
          |    "BUILDINGSTATE",
          |    "BUILDINGKIND",
          |    "NAME",
          |    "BUILDINGADDRSSTYPE",
          |    "ADDRESS",
          |    "CARPLACE",
          |    "MANAGER",
          |    "PROPERTYCOMPANYLINKMANID",
          |    "CONTACTMAN",
          |    "CONTACTTEL",
          |    "CONTACTADDRESS",
          |    "USETYPE",
          |    "BUILDINGUSAGE",
          |    "HOUSESTRUCTRUE",
          |    "BUILDINGELEVATOR",
          |    "BUILDINGDASF",
          |    "POLICESTATION",
          |    "POLICEOFFICE",
          |    "REMARKS",
          |    "BOOKERID",
          |    "REGISTRAR",
          |    "REGISTERTIME",
          |    "CREATOR",
          |    "CREATETIME",
          |    "UPDATETIME",
          |    "UPDATER",
          |    "ISVALID",
          |    "ASSISTID",
          |    "ASSISTANTNAME",
          |    "AGENTID",
          |    "AGENTNAME",
          |    "AREAFOROWNER",
          |    "ASSISTANTID",
          |    "BOTTOMAREA",
          |    "BUILDAGE",
          |    "BUILDMATERIAL",
          |    "CREATORGROUP",
          |    "DATABEGIN",
          |    "DATASOURCE",
          |    "DOWNLEVEL",
          |    "HOUSEFOROWNER",
          |    "HOUSENUM",
          |    "MEDIAS",
          |    "OUTSTYLE",
          |    "OWNERID",
          |    "OWNERNAME",
          |    "OWNERTEL",
          |    "UPDATED",
          |    "FIRMER",
          |    "FIRMTIME",
          |    s.gzwsqcode||substr(t.qhareaid,13) as areaid,
          |    "LAYERS",
          |    "BUILDINGAREA",
          |    "TEMP_AREAID",
          |    "TEMP_ID",
          |    "OTHERADDRESS",
          |    "USESTATE",
          |    "UPLEVEL",
          |    t.areaid as QHAREAID,
          |    "LABELTAG",
          |    "DELMARK",
          |    "TEMP_AREAID2",
          |    "UPDATE_KEY",
          |    "OWNERADDRESS",
          |    "MANAGETYPE",
          |    "CONTROLLER",
          |    "TEMP_BAID",
          |    "FLOORLEADERID",
          |    "UTSERVERLETLOGSTATE",
          |    "UTSERVERLETLOGISUPDATE",
          |    "WLGYID",
          |    "WLGYREMARKS",
          |    "WLGYCODE",
          |    "WLGYNAME",
          |    "WLGYADDRESS",
          |    "CUE",
          |    "LASTUPDATOR",
          |    "CLOUDAPPID",
          |    "BUILDINGID",
          |    "REGISTRACLASS",
          |    "INTERVIEW",
          |    "INTERVIEWTIME",
          |    "INTERVIEWER",
          |    "INTERVIEWREMARK",
          |    "TEAMID",
          |    "BUILDINGRESUME",
          |    "INSERTTIME",
          |    '1' as isaccess,
          |    '1' as isvideo,
          |    '1' as doorplate,
          |    '1' as rentunit,
          |    '1' as isapprove
          |from ${Constants.TMP_LG_BUILDING} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
          |where t.qhareaid<>'0'
        """.stripMargin

    //房间数据同步转换过程的sql
    private val houseSql=
        s"""
          |select
          |    "ID",
          |    "HOUSEUSAGE",
          |    "BUILDINGID",
          |    "BUILDINGNAME",
          |    "HOUSEDESIGNUSEID",
          |    "HOUSEPHOTO",
          |    "CODE",
          |    "HOUSESTAIR",
          |    "NAME",
          |    "HOUSECERTIFICATIONID",
          |    "HOUSECERTIFICATION",
          |    "CERTIFICATIONADDRESS",
          |    "MANAGETYPE",
          |    "HIDDENDANGERTYPE",
          |    "BOOKERID",
          |    "REGISTRAR",
          |    "REGISTERTIME",
          |    "USERID",
          |    "USERNAME",
          |    "HOUSEADDRESS",
          |    "HOUSENAME",
          |    "HOUSETYPEID",
          |    "HOUSESTRUCTRUE",
          |    "REMARKS",
          |    "HOUSELINKMANID",
          |    "HOUSELINKMAN",
          |    "HOUSELINKMANADDRESS",
          |    "HOUSELINKMANTEL",
          |    s.gzwsqcode||substr(qhareaid,13) as areaid,
          |    "AGENTID",
          |    "AGENTNAME",
          |    "AGENTADDRESS",
          |    "AGENTTEL",
          |    "AGENTSTARTDATE",
          |    "AGENTENDDATE",
          |    "CREATOR",
          |    "CREATETIME",
          |    "UPDATETIME",
          |    "UPDATER",
          |    "ISVALID",
          |    "HOUSETENANCYAREA",
          |    "OWNERID",
          |    "OWNERNAME",
          |    "OWNERADDRESS",
          |    "BUILDINGADDRESS",
          |    "BUILDINGAREA",
          |    "BUILDINGTYPE",
          |    "CREATORGROUP",
          |    "DATABEGIN",
          |    "DATASOURCE",
          |    "HOUSEWORTH",
          |    "LASTUPDATEDATE",
          |    "LASTUPDATOR",
          |    "MEDIAS",
          |    "UPDATED",
          |    "UPDATERGROUP",
          |    "BUILDINGLAYERS",
          |    "RESPONSIBILITY",
          |    "FIRMER",
          |    "FIRMTIME",
          |    "HOUSEAREA",
          |    "LIVEAREA",
          |    "BUSINESSAREA",
          |    "OFFICERAREA",
          |    "FACTORYAREA",
          |    "STOREAREA",
          |    "OTHERAREA",
          |    "OWNAREA",
          |    "UNKNOWNAREA",
          |    "HOMEADDRESS2",
          |    "TEMP_ID",
          |    "TEMP_AREAID",
          |    "CONSIGNOR",
          |    "CERTIFICATIONMAN",
          |    "DELMARK",
          |    "UPDATE_KEY",
          |     t.areaid as qhareaid,
          |    "OWNERTEL",
          |    "CONTROLLER",
          |    "HOUSEDEUSE",
          |    "PAPERTYPE",
          |    "CARDNO",
          |    "AGENTCARDTYPE",
          |    "AGENTCARDNO",
          |    "TEMP_BAID",
          |    "OLDCODE",
          |    "OLDBUILDINGID",
          |    "OLDBUILDINGCODE",
          |    "WLGYREMARKS",
          |    "OLDBUILDINGNAME",
          |    "CLOUDAPPID",
          |    "HOUSEID",
          |    "INTERVIEW",
          |    "INTERVIEWTIME",
          |    "INTERVIEWER",
          |    "INTERVIEWREMARK",
          |    "INSERTTIME"
          |from ${Constants.TMP_LG_HOUSE} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
        """.stripMargin

    private val residentSql=
        s"""
          |select
          |    '1'||"ID" ID,
          |    "NATIONALITY",
          |    "NAME",
          |    "ALIAS",
          |    "CARDTYPEID",
          |    "CARDNO",
          |    "EFFECTIVEDATE",
          |    "ISSUINGORGAN",
          |    "SEX",
          |    "NATIONID",
          |    "NATIVEPLACE",
          |    "REGISTERTYPE",
          |    "MARRYID",
          |    "REGISTERADDTYPEID",
          |    "EDULEVELID",
          |    "POLITICSID",
          |    "REGISTERADDRESS",
          |    "BELIEFID",
          |    "PHOTO",
          |    "MANAGETYPE",
          |    "TRADEID",
          |    "JOBID",
          |    "TECHTITLEID",
          |    "BIDEFASHION",
          |    "DOMICILETYPE",
          |    "COMPANY",
          |    "COMPANYADDRESS",
          |    "COMPANYTEL",
          |    "CPOLICESTATION",
          |    "TEL",
          |    "MTEL",
          |    "EMAIL",
          |    "INTIME1",
          |    "ADDTIME",
          |    "REGISTRAR",
          |    "REMARKS",
          |    "CREATOR",
          |    "CREATETIME",
          |    "UPDATETIME",
          |    "UPDATER",
          |    "ISVALID",
          |    "LEASEREASONID",
          |    "DEGREE",
          |    "BLOODTYPEID",
          |    "ESCUAGEID",
          |    "FINISHSCHOOL",
          |    "REFISTERADDRESSPRE",
          |    "REGISTERPLACE",
          |    "SPECIALITY",
          |    "LEASERELATIONID",
          |    "HEALTH",
          |    "HOMEADDRESS",
          |    s.gzwsqcode||substr(t.qhareaid,13) as areaid,
          |    "HOUSEID",
          |    "DIEDATE",
          |    "MARRYDATE",
          |    "RAPOSTALCODE",
          |    "RESIDEINADDRESS",
          |    "RESIDEINPOSTALCODE",
          |    "SINGLEKID",
          |    "RESIDENTTYPE",
          |    "HEIGHT2",
          |    "SALARY2",
          |    "BIRTHDAY",
          |    "UPDATELIVECODE",
          |    "HOMEADDRESSNEW",
          |    "HOUSECODE",
          |    "ISLOGOUT",
          |    "ISTRANSACT",
          |    "TRANSACTTIME",
          |    "TRANSACTOR",
          |    "ISREWRITEADDRESS",
          |    "REWRITETIME",
          |    "REWRITEPERSON",
          |    "TEMP_AREAID",
          |    "TEMP_ID",
          |    "TEMP_MINGZU",
          |    "LETDUTYBOOK",
          |    "DUTYNAME",
          |    "LOGOUTOR",
          |    "LOGOUTTIME",
          |    "TEMP_PHOTO",
          |    "ISINHABITATION",
          |    "ISADDRESSNOT",
          |    "ISLESSEE",
          |    "LEAVEDATE",
          |    "CHECKINDATE",
          |    "PROCREATEDYNAMIC",
          |    "CONTRACEPTIVE",
          |    "CARDHOLDERS",
          |    "VOUCHER",
          |    "VOUCHERCODENUM",
          |    "VOUCHERPHONE",
          |    "OUTREASON",
          |    "NEWAREAID",
          |    "CARDNO2",
          |    "UPDATE_KEY",
          |    "ISSTAT",
          |    "ISHOUSEOUT",
          |    t.areaid as QHAREAID,
          |    "TEMP_HOSUEID",
          |    "TEMP_FANGHAO",
          |    "TMEP_FH",
          |    "TEMP_EDIT",
          |    "TEMP_DELETE",
          |    "HEIGHT",
          |    "SALARY",
          |    "RKSBID",
          |    "ISCARD",
          |    "REGISTERADDRESSDETAIL",
          |    "POPULATIONID",
          |    "CLOUDAPPID",
          |    "INSERTTIME",
          |    '1' "DATA_SOURCES",
          |    null GAT_REMARK
          |from ${Constants.TMP_LG_RESIDENT} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
          |where t.islogout = 0
        """.stripMargin

    private val residenthmtSql=
        s"""
          |select
          |    '2'||"ID" ID,
          |    null "NATIONALITY",
          |    "NAME",
          |    null "ALIAS",
          |    "CARDTYPEID",
          |    "CARDNO",
          |    null "EFFECTIVEDATE",
          |    "ISSUINGORGAN",
          |    "SEX",
          |    null "NATIONID",
          |    null "NATIVEPLACE",
          |    null "REGISTERTYPE",
          |    "MARRYID",
          |    null "REGISTERADDTYPEID",
          |    null "EDULEVELID",
          |    null "POLITICSID",
          |    null "REGISTERADDRESS",
          |    "BELIEFID",
          |    "PHOTO",
          |    null "MANAGETYPE",
          |    "TRADEID",
          |    null "JOBID",
          |    null "TECHTITLEID",
          |    "BIDEFASHION",
          |    "DOMICILETYPE",
          |    "COMPANY",
          |    "COMPANYADDRESS",
          |    "COMPANYTEL",
          |    null "CPOLICESTATION",
          |    null "TEL",
          |    "MTEL",
          |    null "EMAIL",
          |    "INTIME1",
          |    "ADDTIME",
          |    "REGISTRAR",
          |    "REMARKS",
          |    "CREATOR",
          |    "CREATETIME",
          |    "UPDATETIME",
          |    "UPDATER",
          |    null "ISVALID",
          |    "LEASEREASONID",
          |    null "DEGREE",
          |    "BLOODTYPEID",
          |    null "ESCUAGEID",
          |    null "FINISHSCHOOL",
          |    null "REFISTERADDRESSPRE",
          |    null "REGISTERPLACE",
          |    null "SPECIALITY",
          |    null "LEASERELATIONID",
          |    null "HEALTH",
          |    "HOMEADDRESS",
          |    s.gzwsqcode||substr(t.areaid,13) as areaid,
          |    "HOUSEID",
          |    null "DIEDATE",
          |    null "MARRYDATE",
          |    null "RAPOSTALCODE",
          |    null "RESIDEINADDRESS",
          |    null "RESIDEINPOSTALCODE",
          |    null "SINGLEKID",
          |    "RESIDENTTYPE",
          |    null "HEIGHT2",
          |    null "SALARY2",
          |    "BIRTHDAY",
          |    null "UPDATELIVECODE",
          |    null "HOMEADDRESSNEW",
          |    "HOUSECODE",
          |    "ISLOGOUT",
          |    null "ISTRANSACT",
          |    null "TRANSACTTIME",
          |    null "TRANSACTOR",
          |    null "ISREWRITEADDRESS",
          |    null "REWRITETIME",
          |    null "REWRITEPERSON",
          |    null "TEMP_AREAID",
          |    null "TEMP_ID",
          |    null "TEMP_MINGZU",
          |    null "LETDUTYBOOK",
          |    null "DUTYNAME",
          |    "LOGOUTOR",
          |    "LOGOUTTIME",
          |    null "TEMP_PHOTO",
          |    null "ISINHABITATION",
          |    null "ISADDRESSNOT",
          |    null "ISLESSEE",
          |    "LEAVEDATE",
          |    "CHECKINDATE",
          |    null "PROCREATEDYNAMIC",
          |    null "CONTRACEPTIVE",
          |    null "CARDHOLDERS",
          |    null "VOUCHER",
          |    null "VOUCHERCODENUM",
          |    null "VOUCHERPHONE",
          |    "OUTREASON",
          |    "NEWAREAID",
          |    null "CARDNO2",
          |    "UPDATE_KEY",
          |    "ISSTAT",
          |    null "ISHOUSEOUT",
          |    t.areaid as QHAREAID,
          |    null "TEMP_HOSUEID",
          |    null "TEMP_FANGHAO",
          |    null "TMEP_FH",
          |    "TEMP_EDIT",
          |    "TEMP_DELETE",
          |    null "HEIGHT",
          |    "SALARY",
          |    "RKSBID",
          |    null "ISCARD",
          |    null "REGISTERADDRESSDETAIL",
          |    null "POPULATIONID",
          |    null "CLOUDAPPID",
          |    "INSERTTIME",
          |    '2' "DATA_SOURCES",
          |    t.district GAT_REMARK
          |from ${Constants.TMP_LG_RESIDENTHMT} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
          |where t.islogout = 0 and  t.id not in ('ac211711aa6041ef8fa1c0687b17bc82',
          |'ba561d3279e64be582d2b6426a53bc05','FFBA0076C2904F0BAA8AA1B3B007CD26','45793e889db54f5995e44dee6a1dd30a','5418eb4ea8e0458e9592219924d1b6d4','38bc191c4967449bbb3a6e9e5c8c93f1')
        """.stripMargin

    private val residentoutsideSql=
        s"""
          |select
          |    '3'||"ID"|| ID ID,
          |    "NATIONALITY",
          |    "ENNAME" NAME,
          |    "CNNAME" ALIAS,
          |    "CARDTYPEID",
          |    "CARDNO",
          |    null "EFFECTIVEDATE",
          |    null "ISSUINGORGAN",
          |    "SEX",
          |    null "NATIONID",
          |    null "NATIVEPLACE",
          |    null "REGISTERTYPE",
          |    null "MARRYID",
          |    null "REGISTERADDTYPEID",
          |    null "EDULEVELID",
          |    null "POLITICSID",
          |    null "REGISTERADDRESS",
          |    null "BELIEFID",
          |    "PHOTO",
          |    null "MANAGETYPE",
          |    null "TRADEID",
          |    null "JOBID",
          |    null "TECHTITLEID",
          |    null "BIDEFASHION",
          |    null "DOMICILETYPE",
          |    null "COMPANY",
          |    null "COMPANYADDRESS",
          |    null "COMPANYTEL",
          |    null "CPOLICESTATION",
          |    null "TEL",
          |    null "MTEL",
          |    null "EMAIL",
          |    null "INTIME1",
          |    "ADDTIME",
          |    "REGISTRAR",
          |    "REMARKS",
          |    "CREATOR",
          |    "CREATETIME",
          |    "UPDATETIME",
          |    "UPDATER",
          |    "ISVALID",
          |    null "LEASEREASONID",
          |    null "DEGREE",
          |    null "BLOODTYPEID",
          |    null "ESCUAGEID",
          |    null "FINISHSCHOOL",
          |    null "REFISTERADDRESSPRE",
          |    null "REGISTERPLACE",
          |    null "SPECIALITY",
          |    null "LEASERELATIONID",
          |    null "HEALTH",
          |    "HOMEADDRESS",
          |    s.gzwsqcode||substr(t.qhareaid,13) as AREAID,
          |    "HOUSEID",
          |    null "DIEDATE",
          |    null "MARRYDATE",
          |    null "RAPOSTALCODE",
          |    null "RESIDEINADDRESS",
          |    null "RESIDEINPOSTALCODE",
          |    null "SINGLEKID",
          |    "RESIDENTTYPE",
          |    null "HEIGHT2",
          |    null "SALARY2",
          |    null "BIRTHDAY",
          |    null "UPDATELIVECODE",
          |    null "HOMEADDRESSNEW",
          |    "HOUSECODE",
          |    "ISLOGOUT",
          |    null "ISTRANSACT",
          |    null "TRANSACTTIME",
          |    null "TRANSACTOR",
          |    null "ISREWRITEADDRESS",
          |    null "REWRITETIME",
          |    null "REWRITEPERSON",
          |    null "TEMP_AREAID",
          |    null "TEMP_ID",
          |    null "TEMP_MINGZU",
          |    null "LETDUTYBOOK",
          |    null "DUTYNAME",
          |    "LOGOUTOR",
          |    "LOGOUTTIME",
          |    null "TEMP_PHOTO",
          |    null "ISINHABITATION",
          |    null "ISADDRESSNOT",
          |    null "ISLESSEE",
          |    "LEAVEDATE",
          |    "CHECKINDATE",
          |    null "PROCREATEDYNAMIC",
          |    null "CONTRACEPTIVE",
          |    null "CARDHOLDERS",
          |    null "VOUCHER",
          |    null "VOUCHERCODENUM",
          |    null "VOUCHERPHONE",
          |    "OUTREASON",
          |    "NEWAREAID",
          |    null "CARDNO2",
          |    "UPDATE_KEY",
          |    "ISSTAT",
          |    null "ISHOUSEOUT",
          |    t.areaid as QHAREAID,
          |    null "TEMP_HOSUEID",
          |    null "TEMP_FANGHAO",
          |    null "TMEP_FH",
          |    "TEMP_EDIT",
          |    "TEMP_DELETE",
          |    null "HEIGHT",
          |    null "SALARY",
          |    null "RKSBID",
          |    null "ISCARD",
          |    null "REGISTERADDRESSDETAIL",
          |    null "POPULATIONID",
          |    null "CLOUDAPPID",
          |    "INSERTTIME",
          |    '3' "DATA_SOURCES",
          |    null gat_remark
          |from ${Constants.TMP_LG_RESIDENTOUTSIDE} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
          |where t.islogout = 0 and t.id not in('c06bdf9ce1fa4b5b9073e27a82146d94')
        """.stripMargin

    private val residenthmtlogoutSql=
        s"""
           |select
           |    t.id,
           |    t.logouttime,
           |    t.inserttime,
           |    t.name,
           |    t.cardtypeid,
           |    t.cardno,
           |    t.sex,
           |    null nationid,
           |    t.marryid,
           |    null edulevelid,
           |    null registeraddress,
           |    null jobid,
           |    t.company,
           |    t.homeaddress,
           |    t.birthday,
           |    t.housecode,
           |    t.leavedate,
           |    t.checkindate,
           |    t.createtime,
           |    t.creator,
           |    t.islogout,
           |    t.houseid,
           |    t.updatetime,
           |    t.registrar,
           |    s.gzwsqcode || substr(t.qhareaid, 13) as areaid,
           |
           |    t.areaid as qhareaid,
           |    '2' data_sources,
           |    t.district  gat_remark
           |from ${Constants.TMP_LG_RESIDENTHMT_L} t
           |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
           |where t.islogout = 1
        """.stripMargin

    private val residentoutsidelogoutSql=
        s"""
           |select
           |    t.id,
           |    t.logouttime,
           |    t.inserttime,
           |    null name,
           |    t.cardtypeid,
           |    t.cardno,
           |    t.sex,
           |    null nationid,
           |    null marryid,
           |    null edulevelid,
           |    null registeraddress,
           |    null jobid,
           |    null company,
           |    t.homeaddress,
           |    null birthday,
           |    t.housecode,
           |    t.leavedate,
           |    t.checkindate,
           |    t.createtime,
           |    t.creator,
           |    t.islogout,
           |    t.houseid,
           |    t.updatetime,
           |    t.registrar,
           |    s.gzwsqcode || substr(t.qhareaid, 13) as areaid,
           |
           |    t.areaid as qhareaid,
           |    '3' data_sources,
           |    null gat_remark
           |from ${Constants.TMP_LG_RESIDENTOUTSIDE_L} t
           |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
           |where t.islogout = 1
        """.stripMargin


    private val residentlogoutSql=
        s"""
           |select
           |    t.id,
           |    t.logouttime,
           |    t.inserttime,
           |    t.name,
           |    t.cardtypeid,
           |    t.cardno,
           |    t.sex,
           |    t.nationid,
           |    t.marryid,
           |    t.edulevelid,
           |    t.registeraddress,
           |    t.jobid,
           |    t.company,
           |    t.homeaddress,
           |    t.birthday,
           |    t.housecode,
           |    t.leavedate,
           |    t.checkindate,
           |    t.createtime,
           |    t.creator,
           |    t.islogout,
           |    t.houseid,
           |    t.updatetime,
           |    t.registrar,
           |    s.gzwsqcode || substr(t.qhareaid, 13) as areaid,
           |
           |    t.areaid as qhareaid,
           |    '1' data_sources,
           |    null gat_remark
           |from ${Constants.TMP_LG_RESIDENTLOGOUT} t
           |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid, 1, 12) = s.pdasqcode
           |where t.islogout = 1
        """.stripMargin

    private val enterpriseSql=
        s"""
          |select
          |    t."ID",
          |    t."BUILDINGHOUSECODE",
          |    t."ENTERPRISEADDRESS",
          |    t."REGISTRAR",
          |    t."ADDTIME",
          |    t."UPDATER",
          |    t."CREATOR",
          |    t."ENTERPRISETYPE",
          |    t."ENTERPRISENAME",
          |    t."ENTERPRISENO",
          |    t."LEGALNAME",
          |    t."LEGALADDRESS",
          |    t."CARDTYPEID",
          |    t."CARDNO",
          |    t."LEGALTELPHONE",
          |    t."ENTERPRISETELPHONE",
          |    t."OPERATORNAME",
          |    t."OPERATORCARDNOTYPE",
          |    t."OPERATORCARDNO",
          |    t."OPERATORADDRESS",
          |    t."OPERATORTELPHONE",
          |    t."OPERATORTYPE",
          |    t."OPERATORPLACEAREA",
          |    t."REMARKS",
          |    s.gzwsqcode||substr(t.qhareaid,13) as areaid,
          |    t."PHOTO1",
          |    t."PHOTO2",
          |    t."PHOTO3",
          |    t."ISSTATE",
          |    t."LOGOUTREASON",
          |    t."LOGOUTOR",
          |    t."LOGOUTTIME",
          |    t."UPDATETIME",
          |    t."CBUITEM",
          |    t."BIZMETHOD",
          |    t."ESTDATE",
          |    t."AUTHDATE",
          |    t."CREATETIME",
          |    t."PHOTOPATH",
          |    t."ORGANCODE",
          |    t."ISNSPECTION",
          |    t."ENTERPRISEAREA",
          |    t."BUSINESSNAME",
          |    t."BUSINESSFILE",
          |    t."BUSINESSFILEDEFINE",
          |    t."PDAGPSX",
          |    t."PDAGPSY",
          |    t."PDABUSNISSALLOTEDTIME",
          |    t."PDATRADETYPE",
          |    t."PDATRADENATURE",
          |    t."PDAECONOMICTYPE",
          |    t."PDAENTERPRISETYPE",
          |    t."PDAREGISTRATIONPLACE",
          |    t."PDAREGISTRATIONSTREETS",
          |    t."PDAREGISTEREDCAPITAL",
          |    t."REGISTERUNIT",
          |    t."UPDATE_KEY",
          |    t."INTERVIEW",
          |    t."INTERVIEWTIME",
          |    t."INTERVIEWER",
          |    t."INTERVIEWREMARK",
          |    t."CLOUDAPPID",
          |    t."LICENSETYPE",
          |    t."RESIDENTTYPE",
          |    t."JYCS",
          |    t."SSZT_JBXX_RECORDID",
          |    t."AREAID" as qhareaid,
          |    t."AREAIDBAK",
          |    t."INSERTTIME",
          |    t.creditcode
          |FROM ${Constants.TMP_LG_ENTERPRISEINFO} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
          |where t.qhareaid not like '440307001000%'
        """.stripMargin

    //法人与楼栋映射关系表
    private val enterpriseMapSql=
        s"""
          |select
          |    "ID",
          |    "CODE",
          |    "PID",
          |    "BUILDINGID",
          |    "HOUSEID",
          |    s.gzwsqcode||substr(t.qhareaid,13) as AREAID,
          |    "ADDRESS",
          |    "UPDATER",
          |    "UPDATETIME",
          |    "CREATOR",
          |    "CREATETIME",
          |    "BUILDING_LEVEL",
          |    "BUILDING_COUNT",
          |    "AREAID" as QHAREAID
          |FROM ${Constants.TMP_LG_ENTERPRISEINFOMAP} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(t.qhareaid,1,12)=s.pdasqcode
          |where t.qhareaid not like '440307001000%'
        """.stripMargin
    //事件的数据提取sql
    private val eventSql =
        s"""
          |select
          |    id,
          |    photo1 as images,
          |    id as conversationid,
          |    pda_cardno as gatherid,
          |    pda_user as gatherer,
          |    buildinghouse,
          |    event_sqr as sqr,
          |    event_dw as dw,
          |    to_char(t.disposaltime,'yyyy-MM-dd hh24:mi:ss') as reporteddate,
          |    disposalperson as creator,
          |    disposaltimes as comenum,
          |    eventsource as source,
          |    appealaim as aim,
          |    appealnums as reportednum,
          |    place as place,
          |    personnum as amount,
          |    eventsummary,
          |    problem as content,
          |    isrental as ishouse,
          |    iscolony as isgroup,
          |    case when emergencylevel = '01' then '1'
          |        when emergencylevel = '02' then '2'
          |        when emergencylevel = '03' then '3'
          |    end emergency,
          |    eventtype,
          |    substr(eventtype,2,1) kind,
          |    substr(eventtype,2,3) subkind,
          |    substr(eventtype,2,6) threekind,
          |    gis_x||';'||gis_y as location,
          |    gis_x as gisp_x,
          |    gis_y as gisp_y,
          |    codetypename,
          |    to_char(areaid) as areaid,
          |    s.gzwsqcode||substr(to_char(t.areaid),13,3) districtcode,
          |    s.gzwsqcode||substr(to_char(t.areaid),13,3) acceptdepartment,
          |    substr(s.gzwsqcode||substr(to_char(t.areaid),13,3),1,15) gridid
          |from ${Constants.TMP_LG_EVENT} t
          |left join ${Constants.GZW_SQ_QH_SHIBAN} s on substr(to_char(t.areaid),1,12)=s.pdasqcode
          |where t.id <> 'fc8dffa04c8b40278841a651fdd9249e'
        """.stripMargin

    //    val
    def getSqlByJobName(jobName: String) ={
        var resultSql=""
        jobName match {
            case "building" => resultSql=buildSql
            case "house" => resultSql=houseSql
            case "resident" => resultSql=residentSql
            case "residenthmt" => resultSql=residenthmtSql
            case "residentoutside" => resultSql=residentoutsideSql
            case "residenthmtlogout" => resultSql=residenthmtlogoutSql
            case "residentoutsidelogout" => resultSql=residentoutsidelogoutSql
            case "residentlogout" => resultSql=residentlogoutSql
            case "enterprise" => resultSql=enterpriseSql
            case "enterpriseinfomap" => resultSql=enterpriseMapSql
            case "event" => resultSql=eventSql
        }
        resultSql
    }
}

